﻿/****** Script for SelectTopNRows command from SSMS  ******/
if exists(SELECT * FROM sys.objects o WHERE o.[object_id]= OBJECT_ID('V_Product') AND o.[type]=N'V')
BEGIN
	DROP VIEW V_Product
END
GO
create view V_Product
as 
SELECT A. [Guid]
      ,A.[Name]
      ,A.[OriginalImge]
      ,A.[ThumbImage]
      ,A.[SmallImage]
      ,A.[RepertoryNumber]
      ,A.[Weight]
      ,A.[RepertoryCount]
      ,A.[UnitName]
      ,A.[RepertoryAlertCount]
      ,A.[PresentScore]
      ,A.[PresentRankScore]
      ,A.[SocreIntegral]
      ,A.[LimitBuyCount]
	  ,A.[MarketPrice]
      ,A.[ShopPrice]
      ,A.[Brief]
      ,A.[Detail]
      ,A.[ClickCount]
      ,A.[CollectCount]
      ,A.[BuyCount]
      ,A.[CommentCount]
      ,A.[ReferCount]
	  ,A.[ModifyTime]
      ,A.[IsSaled]
      ,A.[IsBest]
      ,A.[IsNew]
      ,A.[IsHot]
      ,A.[IsReal]
      ,A.[IsOnlySaled]
      ,A.[Title]
      ,A.[Description]
      ,A.[Keywords]
      ,A.[OrderID]
      ,A.[BrandName]
      ,A.[ProductCategoryID]
      ,A.[SaleNumber]
      ,A.[ActiveImage]
      ,A.[SupplierLoginID]
	  ,B.[BaskOrderLogCount]
  FROM [ShopNum1_Product] A left join 
  (SELECT [ProductGuid],count(*) as [BaskOrderLogCount]
  FROM [dbo].[ShopNum1_BaskOrder_Log] group by [ProductGuid]) B
  on(A.[Guid]=B.[ProductGuid])
  where [IsDeleted]=0